
from django.utils.timezone import utc, localtime
from django_bulk_update.helper import bulk_update
from orionbase.common import orion_function
from orionbase.common import orion_errors
from django.db import connection
import logging
import sys
from orionbase.common.models import CustomObjects2
from django.db.models import Q
from orionbase.common import models
from orionbase.common.models import CustomObjects5

reload(sys)
sys.setdefaultencoding('utf8')

logger = logging.getLogger('orion.batchupload')


def add_errors(error_ids, messsage, errors):
    if len(error_ids) > 0:
        error_dict = {}
        error_dict['message'] = messsage
        error_dict['ids'] = error_ids
        errors.append(error_dict)
    return errors


if validation_type == 'bulk_post_save':
    logger.info('speaker_data_import start.')
    # 讲者上线
    co2 = CustomObjects2.objects.filter(deleted=0)
    speaker_cnt_limit = 0
    if co2:
        speaker_cnt_limit = co2[0].number_2
        speaker_cnt_limit = int (speaker_cnt_limit)
    logger.info('speaker_cnt_limit : \%s .', speaker_cnt_limit)

    speaker_cnts = models.Speakers.objects.filter(state__name='effective').count()
    logger.info('speaker_cnts : \%s .', speaker_cnts)

    speaker_dcr_cnts = models.SpeakerDCRs.objects.filter(state__name='sent_approval').count()
    logger.info('speaker_dcr_cnts : \%s .', speaker_dcr_cnts)

    geographies_map = dict(models.Geographies.objects.filter(deleted=0).values_list('fullname', 'id'))

    import_id = None
    error_cnt_ids = []
    error_ids = []
    # 省市区不存在
    error_pro_city_ids = []
    import_cnt = 0
    certificate_nos = []
    for instance in objects:
        import_id = instance.import_id.id
        action = instance.action
        is_professional = instance.is_professional
        _id = instance.id
        # 省
        province_name = instance.field4
        # 市
        city_name = instance.field5
        # 区
        county_name = instance.field6
        certificate_nos.append(instance.certificate_no)

        # 非医生讲者
        if is_professional and is_professional == 0:
            # 判断省市区在数据库中是否存在
            if province_name and city_name and county_name:
                pro_city_county = u'{},{},{}'.format(province_name, city_name, county_name)
                geographies = geographies_map.get(pro_city_county.decode('utf-8'), None)
                if not geographies:
                    error_pro_city_ids.append(_id)

        if action == 'I':
            total_cnt = speaker_cnts + speaker_dcr_cnts + import_cnt
            if speaker_cnt_limit > 0 and int(total_cnt) > int(speaker_cnt_limit):
                error_cnt_ids.append(_id)
            import_cnt = import_cnt + 1

    logger.info('import_cnt : \%s .', import_cnt)

    errors = []
    msg = u'申请讲者超过可以申请的上限{speaker_cnt_limit}'.format(speaker_cnt_limit=speaker_cnt_limit)
    # add_errors(error_cnt_ids, msg, errors)
    # add_errors(error_pro_city_ids, u'省市区/县在系统中不存在;', errors)

    if len(errors) > 0:
        logger.info('speaker_data_import errors.')
        raise orion_errors.create_validation_trigger_rule_errors(errors)

    logger.info('import_id : \%s .', import_id)
    if import_id:
        with connection.cursor() as cursor:
            # 非医生讲者
            update_sql = u"""
                update speakers sp
                join (select import_id,certificate_no,is_professional,professional_id,CONCAT(field4, ',', field5, ',', field6) fullname from speakers_import_data where import_id = {import_id}) spid
                left join geographies ge ON spid.fullname = ge.fullname
                set sp.geography_id = ge.id
                where sp.deleted = 0
                and sp.certificate_no = spid.certificate_no
                and spid.import_id = {import_id}
                and sp.is_professional = 0 
                and sp.import_id= spid.import_id
            """.format(import_id=import_id)
            logger.info('speaker_data_import update_sql \%s .', update_sql)
            cursor.execute(update_sql)

            # 医生讲者
            update_sql_1 = u"""
                update speakers sp
                join (select email,phone,import_id,certificate_no,is_professional,professional_id,CONCAT(field4, ',', field5, ',', field6) fullname from speakers_import_data where import_id = {import_id}) spid
                left join geographies ge ON spid.fullname = ge.fullname
                left join accounts acc on acc.id = spid.professional_id
                set sp.name = acc.name,
                sp.gender_id = acc.gender,

                sp.department_id = acc.standard_department_id,
                sp.administrative_title_id = acc.administrator_title,
                sp.professional_title_id = acc.professional_title,
                sp.phone = spid.phone,
                sp.email = spid.email,
                sp.geography_id = (CASE
                    WHEN acc.geography_id is null THEN ge.id
                    ELSE acc.geography_id
                END)

                where sp.deleted = 0
                and sp.certificate_no = spid.certificate_no
                and spid.import_id = {import_id}
                and sp.is_professional = 1
                and sp.import_id = spid.import_id
            """.format(import_id=import_id)

            logger.info('speaker_data_import update_sql_1 \%s .', update_sql_1)
            cursor.execute(update_sql_1)

            # 相关值更新
            _speakers_to_change = []
            _speakers = models.Speakers.objects.filter(deleted=0, importation=import_id).values_list('id', 'record_type__name', 'picklist_2_id', 'professional_id', 'professional__hospital__external_id', 'administrative_title__label', 'department__label', 'professional_title__label', 'level_id', 'professional__picklist_14', 'picklist_14', 'certificate_no', 'department_id', 'picklist_8__label', 'picklist_9__label', 'picklist_10__label', 'boolean_1', 'veeva_code', 'external_id')
            for \
            (_id, record_type__name, picklist_2_id, professional_id, hospital_external_id, administrative_title__label,
            department__label, professional_title__label, level_id, professional__picklist_14, picklist_14,
            certificate_no, _department_id, picklist_8__label, picklist_9__label, picklist_10__label, boolean_1,
            veeva_code, external_id) in _speakers:
                change_sp = models.Speakers()
                change_sp.id = _id
                change_sp.level_id = level_id
                change_sp.external_id = external_id
                if not external_id and veeva_code:
                    change_sp.external_id = veeva_code
                    logger.info('speaker_data_import external_id %s .', change_sp.external_id)
                # 讲者级别(L1/L2)
                if record_type__name in ['cs_professional_ka_l', 'cs_professional_sales_l']:

                    logger.info('speaker_data_import professional_title__label \%s .', professional_title__label)
                    if professional_title__label in [u'主任医师', u'主任护师', u'主任药师', u'主任检验师', u'主任麻醉师', u'主任技师']:
                        change_sp.level_id = '92676030-0c14-411a-81f5-9f9fc8b690ac'
                    elif professional_title__label in [u'副主任医师', u'副主任护师', u'副主任药师', u'副主任技师', u'副主任检验师', u'副主任麻醉师']:
                        change_sp.level_id = '8adddf52-f279-4589-ae77-4222735e384e'
                    elif professional_title__label in [u'主治医师', u'主管医师', u'主管护师', u'主管药师', u'主管技师', u'主管检验师', u'主管麻醉师']:
                        # change_sp.level_id = '2846c921-28e7-4118-b253-0f186cb86a8a'
                        # 年资- 高年资 : L3
                        if (boolean_1 and boolean_1 == 1):
                            change_sp.level_id = '2846c921-28e7-4118-b253-0f186cb86a8a'

                            # 年资 - 低年资 : L4
                        else:
                            change_sp.level_id = 'f7a481fc-2793-4234-94e5-91bba9c91d8d'
                        logger.info('speaker_data_import level_id \%s .', change_sp.level_id)

                if record_type__name in ['cs_professional_clinical', 'cs_non_professional_clinical']:
                    change_sp.level_id = 'd7f0522e-ddb5-4ae1-af26-d278666410d0'

                # 是否需要付讲课费计算
                # 医生讲者:默认【是】
                change_sp.picklist_2_id = 'cedab16b-f7cb-4d7a-80cf-649316b50308'
                hospital_external_ids = []

                # 现指定不付费的军队医院名单以下5家：
                hospital_external_ids = list(models.CustomObjects5.advance_objects.filter(
                    record_type_id='dedee344-dfac-49e9-975a-cbb04f528878').values_list('text_7', flat=True))
                if hospital_external_id in hospital_external_ids:
                    change_sp.picklist_2_id = '018875a8-5921-4c55-bf6d-81070a2bd6dc'

                    # 2、只看行政职称：所有行政职称是院长，副院长，党委书记
                elif administrative_title__label in [u'院长', u'副院长', u'书记', u'支部书记', u'副书记']:
                    change_sp.picklist_2_id = '018875a8-5921-4c55-bf6d-81070a2bd6dc'

                    # 3、只看科室：所有药剂科
                # elif department__label in [u'药剂科']:
                #    change_sp.picklist_2_id = '018875a8-5921-4c55-bf6d-81070a2bd6dc'

                # 3、只看科室：所有药剂科
                elif department__label in [u'药剂科', u'管理科室_院长办公室', u'管理科室_医保办公室',
                                           u'管理科室_医务科'] and administrative_title__label in [u'主任']:
                    change_sp.picklist_2_id = '018875a8-5921-4c55-bf6d-81070a2bd6dc'

                # 指定付费的人员
                certificate_nos = list(models.CustomObjects5.advance_objects.filter(
                    record_type_id='8bf2d103-0825-4cdd-afc1-ac652d72c83b').values_list('text_7', flat=True))
                if certificate_no in certificate_nos:
                    change_sp.picklist_2_id = 'cedab16b-f7cb-4d7a-80cf-649316b50308'

                # 指定不付费的人员
                certificate_nos = list(models.CustomObjects5.advance_objects.filter(
                    record_type_id='8beb5781-0be2-48c3-8774-03a88bdac37e').values_list('text_7', flat=True))
                if certificate_no in certificate_nos:
                    change_sp.picklist_2_id = '018875a8-5921-4c55-bf6d-81070a2bd6dc'

                change_sp.department_id = _department_id
                # 如果没有非医生讲者设置讲者科室，默认其他
                if not professional_id and not _department_id:
                    change_sp.department_id = '5998ebb9-0216-4ce0-89b2-0e55b1fff97c'

                _speakers_to_change.append(change_sp)
            if _speakers_to_change:
                bulk_update(_speakers_to_change,
                            update_fields=['level', 'picklist_2', 'department', 'external_id'], batch_size=500)

    with connection.cursor() as cursor:
        # 医生讲者
        update_sql_external_id = u"""
            update speakers
            set external_id = veeva_code
            where external_id is null and veeva_code is not null;
        """

        logger.info('speaker_data_import update_sql_external_id %s .', update_sql_external_id)
        cursor.execute(update_sql_external_id)
    logger.info('speaker_data_import end.')

